package br.com.revenda.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.revenda.conexao.conectionFactory;
import br.com.revenda.modelo.Funcionario;

public class FuncionarioDAO {

	private Connection conn;
	
	public FuncionarioDAO(){
		this.conn = new conectionFactory().getConnection();
	}
	
	
	public void salvar(Funcionario f){
		String sql = "insert into funcionario (nomeFuncionario , cpf , fone , celular , cep , endereco , cidade , estado , idCargo , dataAdmi)values(?,?,?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setString(1, f.getNome());
			pstm.setString(2, f.getCpf());
			pstm.setString(3, f.getFone());
			pstm.setString(4, f.getCelular());
			pstm.setString(5, f.getCep());
			pstm.setString(6, f.getEndereco());
			pstm.setString(7, f.getCidade());
			pstm.setString(8, f.getEstado());
			pstm.setInt(9, f.getIdCargo());
			pstm.setString(10,  f.getDataAdmi());
			pstm.execute();
			pstm.close();
			conn.close();			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	 
	
	public List<Funcionario> ListaTodos(){
		List<Funcionario> f = new ArrayList<Funcionario>();
		String sql = "Select * FROM funcionario INNER JOIN cargo ON cargo.idCargo = funcionario.idCargo";
		try {
			
			PreparedStatement pstm = conn.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			
			while(rs.next()){
				Funcionario fn = new Funcionario();
				fn.setIdFuncionario(rs.getInt("idFuncionario"));
				fn.setNome(rs.getString("nomeFuncionario"));
				fn.setCpf(rs.getString("cpf"));
				fn.setFone(rs.getString("fone"));
				fn.setCelular(rs.getString("celular"));
				fn.setCep(rs.getString("cep"));
				fn.setEndereco(rs.getString("endereco"));
				fn.setCidade(rs.getString("cidade"));
				fn.setEstado(rs.getString("estado"));
				fn.getCargo().setIdCargo(rs.getInt("idCargo"));
				fn.getCargo().setNome(rs.getString("nomeCargo"));
				fn.getCargo().setSalario(rs.getDouble("salario"));
				fn.setDataAdmi(rs.getString("dataAdmi"));				
				f.add(fn);					
			}
			rs.close();
			pstm.close();
			conn.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return f;
	}
	
	
	public List<Funcionario> ListaEntregador(){
		List<Funcionario> f = new ArrayList<Funcionario>();
		String sql = "Select * FROM funcionario INNER JOIN cargo ON cargo.idCargo = funcionario.idCargo where nomeCargo=?";
		try {
			
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setString(1, "Entregador");
			ResultSet rs = pstm.executeQuery();
			
			while(rs.next()){
				Funcionario fn = new Funcionario();
				fn.setIdFuncionario(rs.getInt("idFuncionario"));
				fn.setNome(rs.getString("nomeFuncionario"));
				fn.getCargo().setIdCargo(rs.getInt("idCargo"));
				fn.getCargo().setNome(rs.getString("nomeCargo"));
				f.add(fn);					
			}
			rs.close();
			pstm.close();
			conn.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return f;
	}
	
	
	
	public void deletar(Funcionario f){
		String sql = "delete from funcionario where idFuncionario = ?";
		try {
			PreparedStatement pstm = conn.prepareStatement(sql);
			pstm.setInt(1, f.getIdFuncionario());
			pstm.execute();
			pstm.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public void alterar(Funcionario f){
	String sql = "update funcionario  set  nomeFuncionario=? , cpf=? , fone=? , celular=? , cep=? , endereco=? , cidade=? , estado=? , dataAdmi=? , idCargo=? where idFuncionario=?  ";
	try {
		PreparedStatement pstm = conn.prepareStatement(sql);
		pstm.setString(1, f.getNome());
		pstm.setString(2, f.getCpf());
		pstm.setString(3, f.getFone());
		pstm.setString(4, f.getCelular());
		pstm.setString(5, f.getCep());
		pstm.setString(6, f.getEndereco());
		pstm.setString(7, f.getCidade());
		pstm.setString(8, f.getEstado());
		pstm.setString(9,  f.getDataAdmi());
		pstm.setInt(10, f.getIdCargo());
		pstm.setInt(11, f.getIdFuncionario());
		pstm.execute();
		pstm.close();
		conn.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
	
	}
	
	
}
